﻿using System;
using System.Collections.Generic;
using System.Text;
using MySql.Data.MySqlClient;
using Zyg.Utility.DBUtility;
using Zyg.SSO.Core.Business;

namespace Zyg.SSO.Core.Providers.DALProviders
{
    public partial class MySqlProvider : DALProvider
    {
        #region SqlStatements

        private static readonly string SqlSelectAccountById = "SELECT `ID`,`RoleID`,`Email`,`Name`,`Password`,`DateCreated`,`State`,`IsAdmin`,`PictureAddress`,`Telephone`,`PassQuestion`,`PassAnswer`,`Remark` FROM `Account` WHERE `ID` = @ID;set names utf8;";
        private static readonly string SqlSelectAccountByEmail = "SELECT `ID`,`RoleID`,`Email`,`Name`,`Password`,`DateCreated`,`State`,`IsAdmin`,`PictureAddress`,`Telephone`,`PassQuestion`,`PassAnswer`,`Remark` FROM `Account` WHERE `Email` = @Email;set names utf8;";

        private static readonly string SqlSelectAccountsAll = "SELECT `ID`,`RoleID`,`Email`,`Name`,`Password`,`DateCreated`,`State`,`IsAdmin`,`PictureAddress`,`Telephone`,`PassQuestion`,`PassAnswer`,`Remark` FROM `Account`;set names utf8;";
        private static readonly string SqlSelectAccountsByRoleId = "SELECT `ID`,`RoleID`,`Email`,`Name`,`Password`,`DateCreated`,`State`,`IsAdmin`,`PictureAddress`,`Telephone`,`PassQuestion`,`PassAnswer`,`Remark` FROM `Account` WHERE `RoleID` = @RoleID;set names utf8;";

        private static readonly string SqlInsertAccount = "INSERT INTO `Account` (`ID`,`RoleID`,`Email`,`Name`,`Password`,`DateCreated`,`State`,`IsAdmin`,`PictureAddress`,`Telephone`,`PassQuestion`,`PassAnswer`,`Remark`) VALUES (@ID,@RoleID,@Email,@Name,@Password,@DateCreated,@State,@IsAdmin,@PictureAddress,@Telephone,@PassQuestion,@PassAnswer,@Remark);set names utf8;";
        private static readonly string SqlDeleteAccountById = "Delete Account Where `ID` = @ID; Set Names utf8;";

        private static readonly string SqlUpdateAccountById = "UPDATE `Account` SET `RoleID` = @RoleID,`Email` = @Email,`Name` = @Name,`Password` = @Password,`DateCreated` = @DateCreated,`State` = @State,`IsAdmin` = @IsAdmin,`PictureAddress` = @PictureAddress,`Telephone` = @Telephone,`PassQuestion` = @PassQuestion,`PassAnswer` = @PassAnswer,`Remark` = @Remark WHERE `ID` = @ID;set names utf8;";

        #endregion

        public override Zyg.SSO.Core.Business.Account SelectAccount(Guid id)
        {
            MySqlUtility db = new MySqlUtility();

            db.AddParameter("@ID", MySqlDbType.VarChar, id);
            MySqlDataReader reader = db.ExecuteSqlReader(SqlSelectAccountById);

            if (reader != null && !reader.IsClosed && reader.Read())
            {
                Account account = new Account();
                if (!reader.IsDBNull(1)) account.RoleID = reader.GetGuid(1);
                if (!reader.IsDBNull(2)) account.Email = reader.GetString(2);
                if (!reader.IsDBNull(3)) account.Name = reader.GetString(3);
                if (!reader.IsDBNull(4)) account.Password = reader.GetString(4);
                if (!reader.IsDBNull(5)) account.DateCreated = reader.GetDateTime(5);
                if (!reader.IsDBNull(6)) account.State = reader.GetInt32(6);
                if (!reader.IsDBNull(7)) account.IsAdmin = reader.GetBoolean(7);
                if (!reader.IsDBNull(8)) account.PictureAddress = reader.GetString(8);
                if (!reader.IsDBNull(9)) account.Telephone = reader.GetString(9);
                if (!reader.IsDBNull(10)) account.PassQuestion = reader.GetString(10);
                if (!reader.IsDBNull(11)) account.PassAnswer = reader.GetString(11);
                if (!reader.IsDBNull(12)) account.Remark = reader.GetString(12);

                reader.Close();

                return account;
            }
            else
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();

                return null;
            }
        }

        public override Zyg.SSO.Core.Business.Account SelectAccount(string email)
        {
            MySqlUtility db = new MySqlUtility();

            db.AddParameter("@Email", MySqlDbType.VarChar, email);
            MySqlDataReader reader = db.ExecuteSqlReader(SqlSelectAccountByEmail);

            if (reader != null && !reader.IsClosed && reader.Read())
            {
                Account account = new Account();
                if (!reader.IsDBNull(0)) account.Id = reader.GetGuid(0);
                if (!reader.IsDBNull(1)) account.RoleID = reader.GetGuid(1);
                if (!reader.IsDBNull(2)) account.Email = reader.GetString(2);
                if (!reader.IsDBNull(3)) account.Name = reader.GetString(3);
                if (!reader.IsDBNull(4)) account.Password = reader.GetString(4);
                if (!reader.IsDBNull(5)) account.DateCreated = reader.GetDateTime(5);
                if (!reader.IsDBNull(6)) account.State = reader.GetInt32(6);
                if (!reader.IsDBNull(7)) account.IsAdmin = reader.GetBoolean(7);
                if (!reader.IsDBNull(8)) account.PictureAddress = reader.GetString(8);
                if (!reader.IsDBNull(9)) account.Telephone = reader.GetString(9);
                if (!reader.IsDBNull(10)) account.PassQuestion = reader.GetString(10);
                if (!reader.IsDBNull(11)) account.PassAnswer = reader.GetString(11);
                if (!reader.IsDBNull(12)) account.Remark = reader.GetString(12);

                account.MarkOld();

                reader.Close();

                return account;
            }
            else
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();

                return null;
            }
        }

        public override void InsertAccount(Zyg.SSO.Core.Business.Account account)
        {
            MySqlUtility db = new MySqlUtility();

            db.AddParameter("@ID", MySqlDbType.VarChar, account.Id);
            db.AddParameter("@RoleID", MySqlDbType.VarChar, account.RoleID);
            db.AddParameter("@Email", MySqlDbType.VarChar, account.Email);
            db.AddParameter("@Name", MySqlDbType.VarChar, account.Name);
            db.AddParameter("@Password", MySqlDbType.VarChar, account.Password);
            db.AddParameter("@DateCreated", MySqlDbType.DateTime, account.DateCreated);
            db.AddParameter("@State", MySqlDbType.Int32, (int)account.State);
            db.AddParameter("@IsAdmin", MySqlDbType.Bit, account.IsAdmin);
            db.AddParameter("@PictureAddress", MySqlDbType.VarChar, account.PictureAddress);
            db.AddParameter("@Telephone", MySqlDbType.VarChar, account.Telephone);
            db.AddParameter("@PassQuestion", MySqlDbType.VarChar, account.PassQuestion);
            db.AddParameter("@PassAnswer", MySqlDbType.VarChar, account.PassAnswer);
            db.AddParameter("@Remark", MySqlDbType.VarChar, account.Remark);

            db.ExecuteSql(SqlInsertAccount);
        }

        public override void DeleteAccount(Zyg.SSO.Core.Business.Account account)
        {
            MySqlUtility db = new MySqlUtility();

            db.AddParameter("@ID", MySqlDbType.VarChar, account.Id);

            db.ExecuteSql(SqlDeleteAccountById);
        }

        public override void UpdateAccount(Zyg.SSO.Core.Business.Account account)
        {
            MySqlUtility db = new MySqlUtility();

            db.AddParameter("@ID", MySqlDbType.VarChar, account.Id);
            db.AddParameter("@RoleID", MySqlDbType.VarChar, account.RoleID);
            db.AddParameter("@Email", MySqlDbType.VarChar, account.Email);
            db.AddParameter("@Name", MySqlDbType.VarChar, account.Name);
            db.AddParameter("@Password", MySqlDbType.VarChar, account.Password);
            db.AddParameter("@DateCreated", MySqlDbType.DateTime, account.DateCreated);
            db.AddParameter("@State", MySqlDbType.Int32, (int)account.State);
            db.AddParameter("@IsAdmin", MySqlDbType.Bit, account.IsAdmin);
            db.AddParameter("@PictureAddress", MySqlDbType.VarChar, account.PictureAddress);
            db.AddParameter("@Telephone", MySqlDbType.VarChar, account.Telephone);
            db.AddParameter("@PassQuestion", MySqlDbType.VarChar, account.PassQuestion);
            db.AddParameter("@PassAnswer", MySqlDbType.VarChar, account.PassAnswer);
            db.AddParameter("@Remark", MySqlDbType.VarChar, account.Remark);

            db.ExecuteSql(SqlUpdateAccountById);
        }

        public override IList<Zyg.SSO.Core.Business.Account> FillAccounts()
        {
            return FillAccounts(null);
        }

        public override IList<Zyg.SSO.Core.Business.Account> FillAccounts(Zyg.SSO.Core.Business.Role role)
        {
            MySqlUtility db = new MySqlUtility();

            IList<Business.Account> accounts = new List<Business.Account>();

            MySqlDataReader reader;

            if (role != null)
            {
                db.AddParameter("@RoleID", MySqlDbType.VarChar, role.Id.ToString());

                reader = db.ExecuteSqlReader(SqlSelectAccountsByRoleId);
            }
            else
            {
                reader = db.ExecuteSqlReader(SqlSelectAccountsAll);
            }

            if (reader != null)
            {
                while (reader.Read())
                {
                    Account account = new Account();

                    if (!reader.IsDBNull(0)) account.Id = reader.GetGuid(0);
                    if (!reader.IsDBNull(1)) account.RoleID = reader.GetGuid(1);
                    if (!reader.IsDBNull(2)) account.Email = reader.GetString(2);
                    if (!reader.IsDBNull(3)) account.Name = reader.GetString(3);
                    if (!reader.IsDBNull(4)) account.Password = reader.GetString(4);
                    if (!reader.IsDBNull(5)) account.DateCreated = reader.GetDateTime(5);
                    if (!reader.IsDBNull(6)) account.State = reader.GetInt32(6);
                    if (!reader.IsDBNull(7)) account.IsAdmin = reader.GetBoolean(7);
                    if (!reader.IsDBNull(8)) account.PictureAddress = reader.GetString(8);
                    if (!reader.IsDBNull(9)) account.Telephone = reader.GetString(9);
                    if (!reader.IsDBNull(10)) account.PassQuestion = reader.GetString(10);
                    if (!reader.IsDBNull(11)) account.PassAnswer = reader.GetString(11);
                    if (!reader.IsDBNull(12)) account.Remark = reader.GetString(12);

                    account.MarkOld();

                    accounts.Add(account);
                }

                reader.Close();
            }

            return accounts;
        }

        public override IList<Zyg.SSO.Core.Business.Account> FillAccounts(string email, string name, Zyg.SSO.Core.Business.AccountState state, bool? isAdmin, Guid? roleId, DateTime? startTime, DateTime? endTime, int? startIndex, int? endIndex)
        {
            IList<Account> results = new List<Account>();

            MySqlUtility db = new MySqlUtility();

            StringBuilder sql = new StringBuilder();

            #region Build SQL

            bool paged = startIndex.HasValue && endIndex.HasValue;
            paged = false;

            if (paged)
            {
                db.AddParameter("@StartIndex", MySqlDbType.Int32, startIndex.Value);
                db.AddParameter("@EndIndex", MySqlDbType.Int32, endIndex.Value);

                sql.Append("SELECT `ID`,`RoleID`,`Email`,`Name`,`Password`,`DateCreated`,`State`,`IsAdmin`,`PictureAddress`,`Telephone`,`PassQuestion`,`PassAnswer`,`Remark`,`RowNumber` FROM( ");
            }

            sql.Append("SELECT `ID`,`RoleID`,`Email`,`Name`,`Password`,`DateCreated`,`State`,`IsAdmin`,`PictureAddress`,`Telephone`,`PassQuestion`,`PassAnswer`,`Remark` ");

            if (paged)
                sql.Append(",ROW_NUMBER() OVER(ORDER BY DateCreated) as `RowNumber` ");

            sql.Append(" FROM `Account` WHERE 1=1 ");

            if (!string.IsNullOrEmpty(email))
            {
                db.AddParameter("@Email", MySqlDbType.VarChar, email);
                sql.Append("AND `Emial` like '%' + @Email + '%' ");
            }

            if (!string.IsNullOrEmpty(name))
            {
                db.AddParameter("@Name", MySqlDbType.VarChar, name);
                sql.Append(" AND `Name` like '%' + @Name + '%' ");
            }

            if (state.ID != Business.AccountState.All.ID)
            {
                db.AddParameter("@State", MySqlDbType.Int32, (int)state);
                sql.Append(" AND `State` = @State ");
            }

            if (isAdmin.HasValue)
            {
                db.AddParameter("@IsAdmin", MySqlDbType.Bit, isAdmin.Value);
                sql.Append(" AND `IsAdmin` = @IsAdmin ");
            }

            if (roleId.HasValue)
            {
                db.AddParameter("@RoleID", MySqlDbType.VarChar, roleId.Value);
                sql.Append(" AND `RoleID` = @RoleID ");
            }

            if (startTime.HasValue && endTime.HasValue)
            {
                db.AddParameter("@StartTime", MySqlDbType.DateTime, startTime.Value);
                db.AddParameter("@EndTime", MySqlDbType.DateTime, endTime.Value);

                sql.Append(" AND (`DateCreated` BETWEEN @StartTime AND @EndTime) ");
            }

            if (paged)
            {
                sql.Append(" ) AS OrderedAccount WHERE RowNumber between @StartIndex and @EndIndex;");
            }

            #endregion

            MySqlDataReader reader = db.ExecuteSqlReader(sql.ToString());

            if (reader != null)
            {
                while (reader.Read())
                {
                    Account account = new Account();

                    if (!reader.IsDBNull(0)) account.Id = reader.GetGuid(0);
                    if (!reader.IsDBNull(1)) account.RoleID = reader.GetGuid(1);
                    if (!reader.IsDBNull(2)) account.Email = reader.GetString(2);
                    if (!reader.IsDBNull(3)) account.Name = reader.GetString(3);
                    if (!reader.IsDBNull(4)) account.Password = reader.GetString(4);
                    if (!reader.IsDBNull(5)) account.DateCreated = reader.GetDateTime(5);
                    if (!reader.IsDBNull(6)) account.State = reader.GetInt32(6);
                    if (!reader.IsDBNull(7)) account.IsAdmin = reader.GetBoolean(7);
                    if (!reader.IsDBNull(8)) account.PictureAddress = reader.GetString(8);
                    if (!reader.IsDBNull(9)) account.Telephone = reader.GetString(9);
                    if (!reader.IsDBNull(10)) account.PassQuestion = reader.GetString(10);
                    if (!reader.IsDBNull(11)) account.PassAnswer = reader.GetString(11);
                    if (!reader.IsDBNull(12)) account.Remark = reader.GetString(12);

                    account.MarkOld();

                    results.Add(account);
                }

                reader.Close();
            }

            return results;
        }

        public override bool ExistsAccount(string email)
        {
            MySqlUtility db = new MySqlUtility();

            db.AddParameter("@Email", MySqlDbType.VarChar, email);
            MySqlDataReader reader = db.ExecuteSqlReader(SqlSelectAccountByEmail);

            if (reader != null && !reader.IsClosed && reader.Read())
            {
                reader.Close();

                return true;
            }
            else
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();

                return false;
            }
        }
    }
}
